﻿using System;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

/*
 * 
 * Should be finished. Add functions if you're missing some.
 * 
 * 
 * 
 */



namespace MySQLLib
{
    public class Query
    {


        /* Updates a specific row in a table.
         * 
         * table: tablename, in which you want to update a row.
         * Columns: which columns you want to update.
         * newValues: new values for ther columns.
         * where: where sql statement.
         * 
         * returns true/false, depends on wether it's succeded or not.
         * throws an exception if the given column count and newvalue count doesn't match.
         * throws an exception if it can't execute the query.
         */
        public static bool UpdateQuery(MySqlConnection conn, string table, string[] Columns, string[] newValues, string Where)
        {
            string query;
            MySqlCommand command;

            if (Columns.Length != newValues.Length)
            {
                Exception ex = new Exception("Couldn't update data. Column count doesn't match.");
                throw ex;
            }

            query = "UPDATE " + table + " SET ";

            for (int i = 0; i < Columns.Length; i++)
            {
                query += Columns[i] + "='" + newValues[i] + "',";
            }

            query = query.Substring(0, query.Length - 1);

            query += " WHERE " + Where;

            command = new MySqlCommand();
            command.Connection = conn;

            command.CommandText = query;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                Exception ex = new Exception("Couldn't update data. Error: " + e.ToString());
                throw ex;
            }

            return true;
        }


        /* Updates a specific row in a table.
         * 
         * table: tablename, in which you want to update a row.
         * Column: which column you want to update.
         * newValue: new value for ther column.
         * where: where sql statement.
         * 
         * returns true/false, depends on wether it's succeded or not.
         * throws an exception if it can't execute the query.
        */
        public static bool UpdateQuery(MySqlConnection conn, string table, string Column, string newValue, string Where)
        {
            string query;
            MySqlCommand command;

            query = "UPDATE " + table + " SET ";

            query += Column + "='" + newValue + "'";

            query += " WHERE " + Where;

            command = new MySqlCommand();
            command.Connection = conn;

            command.CommandText = query;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                Exception ex = new Exception("Couldn't update data. Error: " + e.ToString());
                throw ex;
            }

            return true;
        }


        /* inserts a new row in a table.
         * 
         * table: tablename, in which you want to insert a row.
         * Columns: which column you want to set values.
         * Values: values for the given columns.
         * 
         * returns true/false, depends on wether it's succeded or not.
         * 
         * throws an exception if the column count and values count doesn't match.
         * throws an exception if it can't execute the query.
        */
        public static bool InsertQuery(MySqlConnection conn, string table, string[] Colums, string[] Values)
        {
            string query;
            MySqlCommand command;

            if (Colums.Length != Values.Length)
            {
                Exception ex = new Exception("Couldn't insert data. Column count doesn't match.");
                throw ex;
            }

            query = "INSERT INTO " + table + " (";

            for (int i = 0; i < Colums.Length; i++)
            {
                query += Colums[i] + ",";
            }

            query = query.Substring(0, query.Length - 1);

            query += ") VALUES (";


            for (int i = 0; i < Values.Length; i++)
            {
                query += "\"" + Values[i] + "\",";
            }

            query = query.Substring(0, query.Length - 1);
            query += ")";

            command = new MySqlCommand();
            command.Connection = conn;

            command.CommandText = query;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                Exception ex = new Exception("Couldn't insert data. Error: " + e.ToString());
                throw ex;
            }

            return true;
        }


        /* Delets a specific row from a table.
         * 
         * table: tablename, in which you want to update a row.
         * where: where sql statement.
         * 
         * returns true/false, depends on wether it's succeded or not.
         * throws an exception if it can't execute the query.
        */
        public static bool DeleteQuery(MySqlConnection conn, string table, string Where)
        {
            string query;
            MySqlCommand command;

            query = "DELETE FROM " + table + " WHERE " + Where;

            command = new MySqlCommand();
            command.Connection = conn;

            command.CommandText = query;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                Exception ex = new Exception("Couldn't delete data. Error: " + e.ToString());
                throw ex;
            }

            return true;
        }

        public static string[,] SelectQuery(MySqlConnection conn, string table, string[] Columns, string Where = "", string Order = "", string limit = "")
        {
            string query;
            MySqlCommand command;
            MySqlDataReader reader;
            string[,] info;
            int count = 0, columncount;


            columncount = getFieldCount(conn, table);
            info = new string[0, columncount];

            query = "SELECT ";

            if (Columns[0] != "*")
            {
                query = "SELECT ";
                for (int i = 0; i < Columns.Length; i++)
                {
                    query += Columns[i] + ",";
                }
                query = query.Substring(0, query.Length - 1);
            }
            else
            {
                query = "SELECT *";
            }

            query += " FROM " + table;

            if (Where.Length > 2)
                query += " WHERE " + Where;

            if (Order.Length > 2)
                query += " ORDER BY " + Order;

            if (limit.Length > 2)
                query += " LIMIT " + limit;

            command = new MySqlCommand();
            command.Connection = conn;

            command.CommandText = query;
            try
            {
                reader = command.ExecuteReader();
                
            }
            catch (MySqlException e)
            {
                Exception ex = new Exception("Couldn't read data. Error: " + e.ToString());
                throw ex;
            }

            while (reader.Read())
            {
                for( int i = 0; i < columncount; i++)
                {
                    if (count == 0)
                        Other.ResizetwoDimensArray(ref info, columncount, i + 1);
                    info[count,i] = reader.GetString(i);
                    
                }

                count++;
            }


            reader.Close();
            reader.Dispose();

            return info;
        }

        public static string[] SelectQuery(MySqlConnection conn, string table, string Columns, string Where, string Order = "",string limit = "")
        {
            string query;
            MySqlCommand command;
            MySqlDataReader reader;
            string[] info = new string[1];
            int count = 0;

            query = "SELECT " + Columns + " FROM " + table + " WHERE " + Where;

            if (Order != "")
                query += " ORDER BY " + Order;

            if (limit != "")
                query += " LIMIT " + limit;

            command = new MySqlCommand();
            command.Connection = conn;

            command.CommandText = query;
            try
            {
                reader = command.ExecuteReader();

            }
            catch (MySqlException e)
            {
                Exception ex = new Exception("Couldn't read data. Error: " + e.ToString());
                throw ex;
            }

            while (reader.Read())
            {
                info[count] = reader.GetString(count);
                Other.ResizeArray(info, info.Length + 1);

                count++;
            }

            reader.Close();
            reader.Dispose();

            return info;
        }

        public static string[] getRowInfo(MySqlConnection conn, string table, string Where)
        {
            string[] info = new string[1];
            string query;
            MySqlCommand command = new MySqlCommand();
            MySqlDataReader reader;
            int count = 0;

            query = "SELECT * FROM " + table + " WHERE " + Where + " LIMIT 1";

            command.Connection = conn;
            command.CommandText = query;

            try
            {
                reader = command.ExecuteReader();
            }
            catch (MySqlException e)
            {
                Exception ex = new Exception("Couldn't get row data. Error: " + e.ToString());
                throw ex;
            }

            while (reader.Read())
            {
                info[count] = reader.GetString(count);
                Other.ResizeArray(info, info.Length + 1);

                count++;
            }

            reader.Close();
            reader.Dispose();

            return info;
        }

        public static int getFieldCount(MySqlConnection conn, string tablename)
        {
            int count = 0;
            MySqlCommand command = new MySqlCommand();
            MySqlDataReader reader;

            command.Connection = conn;
            command.CommandText = "DESCRIBE " + tablename;

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    count++;
                }
            }
            catch (MySqlException)
            {
                return 0;
            }

            reader.Close();
            reader.Dispose();

            return count;
        }

        public static int getRowCount(MySqlConnection conn, string table, string where)
        {
            int count = 0;
            MySqlCommand command = new MySqlCommand();
            MySqlDataReader reader;

            command.Connection = conn;
            command.CommandText = "SELECT * FROM " + table + " WHERE " + where;

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    count++;
                }
            }
            catch (MySqlException e)
            {
                Exception ex = new Exception("Couldn't get data info. Error: " + e.ToString());
                throw ex;
            }


            reader.Close();
            reader.Dispose();

            return count;
        }

        public static string[] getRowData(MySqlConnection conn, string table, int row)
        {
            string query;
            MySqlCommand command;
            MySqlDataReader reader;
            string[] info = new string[1];
            int count = 0;

            query = "SELECT * FROM " + table + " LIMIT " + row + ",1";


            command = new MySqlCommand();
            command.Connection = conn;

            command.CommandText = query;
            try
            {
                reader = command.ExecuteReader();

            }
            catch (MySqlException e)
            {
                Exception ex = new Exception("Couldn't read data. Error: " + e.ToString());
                throw ex;
            }

            while (reader.Read())
            {
                info[count] = reader.GetString(count);
                Other.ResizeArray(info, info.Length + 1);

                count++;
            }

            reader.Close();
            reader.Dispose();

            return info;
        }
    }
}
